﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;

namespace MailBox.App_Code
{
    public class DbHelper
    {
        string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
        public static DbHelper Current;
        public DbHelper()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            //Current = new DbHelper();
        }
        public DataTable GetTable(string sql, params object[] parameters)
        {
            SqlCommand cmd = CreateCommand(sql, parameters);
            DataTable T = new DataTable();
            SqlDataAdapter data = new SqlDataAdapter(cmd);
            data.Fill(T);
            cmd.Connection.Close();
            return T;
        }
        public object GetObject(object result, string sql, params object[] parameters)
        {
            SqlCommand cmd = CreateCommand(sql, parameters);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result = Import(reader, result);
                cmd.Connection.Close();
            }
            return result;
        }
        public int ExecuteNoneQuery(string sql, params object[] parameters)
        {
            int result = 0;
            SqlCommand cmd = CreateCommand(sql, parameters);
            result = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return result;
        }
        public object ExecuteScalar(string sql, params object[] parameters)
        {
            object result = 0;
            SqlCommand cmd = CreateCommand(sql, parameters);
            result = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return result;
        }
        public SqlCommand CreateCommand(string sql, params object[] parameters)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.Trim().IndexOf(' ') < 0)
                cmd.CommandType = CommandType.StoredProcedure;
            if (cmd.Connection.State != ConnectionState.Open)
                conn.Open();
            if (parameters.Length > 0)
            {
                SqlParameter para;
                for (int i = 0; i < parameters.Length; i++)
                {
                    para = new SqlParameter(parameters[i] + "", parameters[i + 1]);
                    cmd.Parameters.Add(para);
                    i += 1;
                }
            }
            return cmd;
        }

        public object Import(IDataReader vReader, object vData)
        {
            string s = "";
           // try
           // {
                this.GetTableInfo(vData.GetType());
                Type T = vData.GetType();
                PropertyInfo[] infos = T.GetProperties();
                string FieldList = GetFieldName(vReader);
                foreach (PropertyInfo info in infos)
                {
                    if (FieldList.IndexOf("," + info.Name.ToLower() + ",") != -1)
                    {
                        if (!vReader.IsDBNull(vReader.GetOrdinal(info.Name)) && !(info.PropertyType == typeof(DateTime?) && vReader[info.Name] != null && Convert.ToDateTime(vReader[info.Name]).ToString("dd/MM/yyyy") == "01/01/0001"))
                        {
                            s = info.Name;
                            info.SetValue(vData, vReader[info.Name], null);
                        }
                        else
                        {
                            if (this.NullableColumns.ContainsKey(info.Name))
                                info.SetValue(vData, null, null);
                        }
                    }
                }
            //}
            //catch (Exception e) { Common.logs(s + "\n" + e.Message, e, Common.logType.error); }
            return vData;
        }
        public string GetFieldName(IDataReader vReader)
        {
            string sTemp = ",";
            for (int i = 0; i < vReader.FieldCount; i++)
            {
                sTemp += vReader.GetName(i).ToLower() + ",";
            }
            return sTemp;
        }
        #region TableInfo
        string _TableName;
        public string TableName
        {
            get { return _TableName; }
        }
        Hashtable _PKeys;
        public Hashtable PKeys
        {
            get { return _PKeys; }
        }
        Hashtable _IdenOrComputeColumns;
        public Hashtable IdenOrComputeColumns
        {
            get { return _IdenOrComputeColumns; }
        }
        Hashtable _NullableColumns;
        public Hashtable NullableColumns
        {
            get { return _NullableColumns; }
            set { _NullableColumns = value; }
        }

        public void GetTableInfo(Type Entites)
        {
            object Obj = Entites.Assembly.CreateInstance(Entites.FullName);
            _TableName = ((SQLTable)(Entites.GetCustomAttributes(true)[0])).TableName;
            PropertyInfo[] infos = Entites.GetProperties();

            Hashtable PK = new Hashtable();
            Hashtable IdenOrCompute = new Hashtable();
            Hashtable NullableCols = new Hashtable();
            foreach (PropertyInfo info in infos)
            {
                if (info.PropertyType.Name.IndexOf("Nullable") != -1)
                {
                    if (!NullableCols.ContainsKey(info.Name))
                        NullableCols.Add(info.Name, info.Name);
                }
                if (info.GetCustomAttributes(true).Length > 0)
                {
                    if (info.GetCustomAttributes(true)[0].ToString().ToLower().IndexOf("pkkey") >= 0)
                    {
                        object pkey = ((PKKey)(info.GetCustomAttributes(true)[0])).Column;
                        if (pkey == null) pkey = info.Name;
                        object value = ((PKKey)(info.GetCustomAttributes(true)[0])).IsIdentityOrCompute;
                        if (!PK.ContainsKey(pkey))
                            PK.Add(pkey, value);
                        if (Convert.ToBoolean(value))
                            if (!IdenOrCompute.ContainsKey(pkey))
                                IdenOrCompute.Add(pkey, value);
                    }

                    if (info.GetCustomAttributes(true)[0].ToString().ToLower().IndexOf("identityorcompute") >= 0)
                    {
                        object ikey = ((IdentityOrCompute)(info.GetCustomAttributes(true)[0])).Column;
                        if (ikey == null) ikey = info.Name;
                        object value = ((IdentityOrCompute)(info.GetCustomAttributes(true)[0])).IsIdentityOrCompute;
                        if (!IdenOrCompute.ContainsKey(ikey))
                            IdenOrCompute.Add(ikey, value);
                    }
                }
            }
            _NullableColumns = NullableCols;
            _PKeys = PK;
            _IdenOrComputeColumns = IdenOrCompute;
        }
        #endregion
    }
    #region Table Attribute
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
    public class SQLTable : Attribute
    {
        string _TableName;
        public SQLTable(string TableName)
        { _TableName = TableName; }

        public string TableName
        {
            get { return _TableName; }
        }

    }
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
    public class SQLTableDM : Attribute
    {
        string _TableName;
        public SQLTableDM(string TableName, string id, string name)
        { _TableName = TableName; ID = id; Name = name; }

        public SQLTableDM(string TableName, string id, string name, string code, string applydate)
        { _TableName = TableName; ID = id; Name = name; Code = code; Applydate = applydate; }

        public string TableName
        {
            get { return _TableName; }
        }
        string _ID;
        public string ID
        {
            get { return _ID; }
            set { _ID = value; }
        }
        string _Name;
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        string _code;
        public string Code
        {
            get { return _code; }
            set { _code = value; }
        }
        string _applydate;
        public string Applydate
        {
            get { return _applydate; }
            set { _applydate = value; }
        }
    }

    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class PKKey : Attribute
    {
        string _Columns;
        bool _IsIdentityOrCompute;

        public PKKey(string Columns, bool IsIdentityOrCompute)
        {
            _Columns = Columns;
            _IsIdentityOrCompute = IsIdentityOrCompute;
        }

        public PKKey(bool IsIdentityOrCompute)
        {
            _IsIdentityOrCompute = IsIdentityOrCompute;
        }

        public string Column
        {
            get { return _Columns; }
        }
        public bool IsIdentityOrCompute
        {
            get { return _IsIdentityOrCompute; }
        }
    }

    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class IdentityOrCompute : Attribute
    {
        string _Columns;
        bool _IsIdentityOrCompute;

        public IdentityOrCompute(string Columns, bool IsIdentityOrCompute)
        {
            _Columns = Columns;
            _IsIdentityOrCompute = IsIdentityOrCompute;
        }

        public IdentityOrCompute(bool IsIdentityOrCompute)
        {
            _IsIdentityOrCompute = IsIdentityOrCompute;
        }

        public string Column
        {
            get { return _Columns; }
        }
        public bool IsIdentityOrCompute
        {
            get { return _IsIdentityOrCompute; }
        }
    }
    #endregion
}